﻿Imports MySql.Data.MySqlClient
Public Class Form2
    Dim con As MySqlConnection
    Dim cmd As New MySqlCommand
    Dim result As Integer
    Dim reader As MySqlDataReader
    Dim dt As New DataTable
    Dim bsource As BindingSource

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=;database=library_database"
        Try
            con.Open()
            Dim query As String
            query = "insert into library_database.book_borrower (book_issue_no,issue_book,return_book,reserve_book,book_borrower_details,book_borrower_notes) values('" & txtbookissueno.Text & "','" & txtissuebook.Text & "','" & txtreturnbook.Text & "','" & txtreservebook.Text & "','" & txtbookborrowerdetails.Text & "','" & txtbookborrowernotes.Text & "') "
            Dim cmd As New MySqlCommand(query, con)
            reader = cmd.ExecuteReader
            While reader.Read
                result = result + 1
            End While
            If result = 0 Then
                MessageBox.Show("data is succesfully saved")
                txtbookissueno.Text = ""
                txtissuebook.Text = ""
                txtreturnbook.Text = ""
                txtreservebook.Text = ""
                txtbookborrowerdetails.Text = ""
                txtbookborrowernotes.Text = ""
                txtbookissueno.Focus()


            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
        

    End Sub

    Private Sub btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn.Click
        Form3.Show()

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Form4.Show()

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim con As New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=;database=library_database"
        Try
            con.Open()
            Dim query As String
            query = "update library_database.book_borrower set book_issue_no='" & txtbookissueno.Text & "',issue_book='" & txtissuebook.Text & "',return_book='" & txtreturnbook.Text & "',reserve_book='" & txtreservebook.Text & "',book_borrower_details='" & txtbookborrowerdetails.Text & "',book_borrower_notes='" & txtbookborrowernotes.Text & "' where book_issue_no='" & txtbookissueno.Text & "' "
            Dim cmd As New MySqlCommand(query, con)
            reader = cmd.ExecuteReader
            MessageBox.Show("data is update")
            txtissuebook.Text = ""
            txtreturnbook.Text = ""
            txtreservebook.Text = ""
            txtbookborrowerdetails.Text = ""
            txtbookborrowernotes.Text = ""
            txtissuebook.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim con As New MySqlConnection("server=localhost;userid=root;password=;database=library_database")
        Try
            con.Open()
            cmd = New MySqlCommand("select * from library_database.book_borrower where book_issue_no='" & txtbookissueno.Text & "' and book_borrower_details='" & txtbookborrowerdetails.Text & "' ", con)
            result = cmd.ExecuteScalar
            con.Close()
            If result >= 1 Then
                con.Open()
                cmd = New MySqlCommand("delete from book_borrower where book_issue_no='" & txtbookissueno.Text & "' and book_borrower_details='" & txtbookborrowerdetails.Text & "' ", con)
                cmd.ExecuteNonQuery()
                MessageBox.Show("data successfully deleted")
                con.Close()
            Else
                MessageBox.Show("no data found")
            End If
            txtbookissueno.Text = ""
            txtissuebook.Text = ""
            txtreturnbook.Text = ""
            txtreservebook.Text = ""
            txtbookborrowerdetails.Text = ""
            txtbookborrowernotes.Text = ""
            txtbookissueno.Focus()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try

    End Sub

   
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label4.Click

    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        con = New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=;database=library_database "
        Dim sda As New MySqlDataAdapter
        Dim bsource As New BindingSource

        Try
            con.Open()
            Dim query As String
            query = "select book_issue_no,issue_book,return_book,reserve_book,book_borrower_details from library_database.book_borrower "
            cmd = New MySqlCommand(query, con)
            sda.SelectCommand = cmd
            sda.Fill(dt)
            bsource.DataSource = dt
            DataGridView1.DataSource = bsource
            sda.Update(dt)
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub


    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch1.TextChanged
        Dim dv As New DataView(dt)
        dv.RowFilter = String.Format("book_borrower_details like '%{0}%' ", txtsearch1.Text)
        DataGridView1.DataSource = dv
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        dt.Clear()
    End Sub
End Class